<?php

class Application_Model_DbTable_ZFUserCountry extends Zend_Db_Table_Abstract {

    protected $_name = 'zf_user_country';
    protected $_allname = 'gm_country';
    protected $country = 'GB';
    protected $language = 'en';

    public function setCountry($count) {
        $this->country = $count;
    }

    public function setLanguage($lang) {
        if ($lang != "") {
            $this->language = $lang;
        }
    }

    public function getAllCountriesWallForUser($userid, $adminGroupID) {
        $lang = $this->language;
        $results = array();
        if ($adminGroupID > 23) {
            $select = $this->_db->select()
                            ->from($this->_allname, array('code_country', 'country_id'))
                            ->where('country_id > ?', 0);
            $results = $this->getAdapter()->fetchAll($select);
        } else {
            $select = $this->_db->select()
                            ->from($this->_name, array('code_country', 'users_id'))
                            ->where('users_id = ?', $userid);
            $results = $this->getAdapter()->fetchAll($select);
        }

// addEmptyElement for dropdowns
        foreach ($results as $key => $element) {
            $q_sessions = "SELECT count(*) as counter FROM zf_sunset WHERE approved = 0 AND country_o_r = '" . $element['code_country'] . "'";
            $result = $this->_db->query($q_sessions);
            while ($number = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['number'] = $number->counter;
            }
            $q_sessions = "SELECT name_" . $lang . " as cname FROM gm_country WHERE code_country = '" . $element['code_country'] . "'";
            $result = $this->_db->query($q_sessions);
            while ($country = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['cname'] = $country->cname;
            }
        }
        #		foreach($results as $key => $element) {
        #		}
        #		Zend_Debug::dump($results, 'number');


        return $results;
        /*
          ->fetchAll($select);
          [0] => array(2) {
          ["code_country"] => string(2) "CA"
          ["name_en"] => string(6) "Canada"
          }
          [1] => array(2) {
          ["code_country"] => string(2) "FR"
          ["name_en"] => string(6) "France"
          }
         */
        #->fetchRow($select);
        /*
          ->fetchPairs($select);
          ["CA"] => string(6) "Canada"
          ["FR"] => string(6) "France"
          ["BR"] => string(6) "Brazil"
         */
    }

    public function getAllCountriesMarkedForUser($userid) {
        $lang = $this->language;

        $q_sessions = "select c.name_$lang as name, c.code_country, uc.users_id as selected from `gm_country` c left join `zf_user_country` uc "
                . " on uc.code_country = c.code_country "
                . " where uc.users_id = $userid or uc.users_id is null ";

        $result = $this->_db->query($q_sessions);
        $results = array();
        $counter = 0;
        while ($data = $result->fetch(Zend_Db::FETCH_OBJ)) {
            if ($data->selected > 0) {
                $sel = "checked";
            } else {
                $sel = "";
            }
            $record = array('name' => $data->name,
                'ccode' => $data->code_country,
                'selected' => $sel);
            $results[$counter] = $record;
            $counter++;
        }
        return $results;

        #Zend_Debug::dump($results, 'countries');
        #Zend_Debug::dump($result->fetch (Zend_Db::FETCH_OBJ), 'countries');
        /*
          while ($session =  $rSess->fetch (Zend_Db::FETCH_OBJ)) {
          $this->_view->conf_session_name = $session->name;
          $this->_view->conf_slot_name = substr($session->slot_begin, 0, 5)
          . "-" . substr($session->slot_end, 0, 5);
          $this->_view->conf_session_comment = $session->sess_comment;
          $this->_view->conf_session_chair = $session->chair;
          $this->_view->conf_session_room = $session->room;
          $this->_view->papers = "";
          }
         */   #
        /*
          select c.name_fr, c.code_country, uc.users_id from `gm_country_live` c left join `zf_user_country` uc
          on uc.code_country = c.code_country
          where uc.users_id = 6 or uc.users_id is null
         */
    }

    public function saveAssign($adminID, $countries) {
        $where = $this->getAdapter()->quoteInto("users_id = ?", $adminID);
        $this->delete($where);

        if ($countries != NULL) {
            foreach ($countries as $val => $key) {
                $insert = array(
                    'code_country' => $key,
                    'users_id' => $adminID
                );
                $this->insert($insert);
            }

            #		Zend_Debug::dump($countries, 'countries');
        }

        #Zend_Debug::dump($countries, 'countries');
    }

    public function getAllCountriesNewsForUser($userid, $adminGroupID = 23) {
        $lang = $this->language;
        $results = array();
        if ($adminGroupID > 23) {
            $select = $this->_db->select()
                            ->from($this->_allname, array('code_country', 'country_id'))
                            ->where('country_id > ?', 0);
            $results = $this->getAdapter()->fetchAll($select);
        } else {
            $select = $this->_db->select()
                            ->from($this->_name, array('code_country', 'users_id'))
                            ->where('users_id = ?', $userid);
            $results = $this->getAdapter()->fetchAll($select);
        }
   #     $select = $this->_db->select()
    #                    ->from($this->_name, array('code_country', 'users_id'))
     #                   ->where('users_id = ?', $userid);
      #  $results = $this->getAdapter()->fetchAll($select);
        // addEmptyElement for dropdowns
        foreach ($results as $key => $element) {

            $q_sessions = "SELECT count(*) as counter FROM zf_news_country WHERE code_country = '" . $element['code_country'] . "'";
            $result = $this->_db->query($q_sessions);
            while ($number = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['number'] = $number->counter;
            }

            $q_sessions = "SELECT name_" . $lang . " as cname FROM gm_country WHERE code_country = '" . $element['code_country'] . "'";
            $result = $this->_db->query($q_sessions);
            while ($country = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['cname'] = $country->cname;
            }
        }

        return $results;
    }

    public function getAllCountriesForUserAndNews($userid, $newsId, $adminGroupID = 23) {
        $lang = $this->language;

         $results = array();
        if ($adminGroupID > 23) {
            $select = $this->_db->select()
                            ->from($this->_allname, array('code_country', 'country_id'))
                            ->where('country_id > ?', 0);
            $results = $this->getAdapter()->fetchAll($select);
        } else {
            $select = $this->_db->select()
                            ->from($this->_name, array('code_country', 'users_id'))
                            ->where('users_id = ?', $userid);
            $results = $this->getAdapter()->fetchAll($select);
        }
          
      //  $select = $this->_db->select()
       //                 ->from($this->_name, array('code_country', 'users_id'))
      //                  ->where('users_id = ?', $userid);
     //   $results = $this->getAdapter()->fetchAll($select);
        // addEmptyElement for dropdowns
        foreach ($results as $key => $element) {

            $q_sessions = "SELECT count(*) as counter FROM zf_news_country WHERE code_country = '" . $element['code_country'] . "' AND news_id = '" . $newsId . "'";
            $result = $this->_db->query($q_sessions);
            while ($number = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['selected'] = $number->counter;
            }

            $q_sessions = "SELECT name_" . $lang . " as cname FROM gm_country WHERE code_country = '" . $element['code_country'] . "'";
            $result = $this->_db->query($q_sessions);
            while ($country = $result->fetch(Zend_Db::FETCH_OBJ)) {
                $results[$key]['cname'] = $country->cname;
            }
        }

        return $results;
    }

}